-------------------------------------------------------------------------------------------------------
-- EXERCICI 1.A
-------------------------------------------------------------------------------------------------------

BEGIN WORK;

------------------ case : insertion in composades ----------------------------------------

CREATE PROCEDURE  st_Update_Receipt_Calories_InsertOnComposades(receiptCode LIKE receptes.codi_recepta, ingredientCode LIKE ingredients.codi_ingredient, quantity LIKE composades.quantitat_en_grams)

	DEFINE calGram int;
	SELECT calories_per_gram INTO calGram FROM ingredients WHERE codi_ingredient = ingredientCode;

	UPDATE receptes SET calories=calories + calGram*quantity 
	WHERE codi_recepta= receiptCode;

END PROCEDURE;


CREATE TRIGGER tg_Update_Calories_InsertOnComposades
  	INSERT ON Composades
  	REFERENCING  NEW AS new
  	FOR EACH ROW (EXECUTE PROCEDURE st_Update_Receipt_Calories_InsertOnComposades(new.codi_recepta, new.codi_ingredient, new.quantitat_en_grams));


------------------- case : delete on composades ------------------------------------------

CREATE PROCEDURE  st_Update_Receipt_Calories_DeleteOnComposades(receiptCode LIKE receptes.codi_recepta, ingredientCode LIKE ingredients.codi_ingredient, quantity LIKE composades.quantitat_en_grams)

	DEFINE calGram int;
	SELECT calories_per_gram INTO calGram FROM ingredients WHERE codi_ingredient = ingredientCode;
	UPDATE receptes SET calories=calories - calGram*quantity 
	WHERE codi_recepta=receiptCode;

END PROCEDURE;


CREATE TRIGGER tg_Update_Calories_DeleteOnComposades
  	DELETE ON Composades
  	REFERENCING  OLD AS old
  	FOR EACH ROW (EXECUTE PROCEDURE st_Update_Receipt_Calories_DeleteOnComposades(old.codi_recepta, old.codi_ingredient, old.quantitat_en_grams));


------------------- case : update field quantity on composades ------------------------------

CREATE PROCEDURE  st_Update_Receipt_Calories_UpdateQuantityOnComposades(receiptCode LIKE receptes.codi_recepta, ingredientCode LIKE ingredients.codi_ingredient, oldQuantity LIKE composades.quantitat_en_grams, newQuantity LIKE composades.quantitat_en_grams)

	DEFINE calGram int;
	SELECT calories_per_gram INTO calGram FROM ingredients WHERE codi_ingredient = ingredientCode;
	UPDATE receptes SET calories=calories - calGram*oldQuantity + calGram*newQuantity
	WHERE codi_recepta=receiptCode;

END PROCEDURE;

CREATE TRIGGER tg_Update_Calories_UpdateOnQuantitat
  	UPDATE OF  quantitat_en_grams ON Composades
  	REFERENCING OLD AS old NEW AS new
  	FOR EACH ROW (EXECUTE PROCEDURE st_Update_Receipt_Calories_UpdateQuantityOnComposades(old.codi_recepta, old.codi_ingredient, old.quantitat_en_grams, new.quantitat_en_grams));



------------------- case : update field codi_ingredient on composades ------------------------------

CREATE PROCEDURE  st_Update_Receipt_Calories_UpdateIngredientCodeOnComposades(receiptCode LIKE receptes.codi_recepta, oldIngredientCode LIKE ingredients.codi_ingredient, newIngredientCode LIKE ingredients.codi_ingredient, quantity LIKE composades.quantitat_en_grams)

	DEFINE calGramOldIngredient   int;
	DEFINE calGramNewIngredient int;

	SELECT calories_per_gram INTO calGramOldIngredient   FROM ingredients WHERE codi_ingredient = oldIngredientCode;
	SELECT calories_per_gram INTO calGramNewIngredient   FROM ingredients WHERE codi_ingredient = newIngredientCode;

	UPDATE receptes SET calories=calories - calGramOldIngredient*quantity + calGramNewIngredient*quantity
	WHERE codi_recepta=receiptCode;

END PROCEDURE;


CREATE TRIGGER tg_Update_Calories_UpdateOnCodiIngredient
  	UPDATE OF  codi_ingredient ON Composades
  	REFERENCING OLD AS old NEW AS new
  	FOR EACH ROW (EXECUTE PROCEDURE st_Update_Receipt_Calories_UpdateIngredientCodeOnComposades(old.codi_recepta, old.codi_ingredient, new.codi_ingredient, old.quantitat_en_grams));



------------------- case : update field codi_recepta on composades ------------------------------

CREATE PROCEDURE  st_Update_Receipt_Calories_UpdateReceiptCodeOnComposades(oldReceiptCode LIKE receptes.codi_recepta, newReceiptCode LIKE receptes.codi_recepta, ingredientCode LIKE ingredients.codi_ingredient, quantity LIKE composades.quantitat_en_grams)

	DEFINE calGram int;

	SELECT calories_per_gram INTO calGram FROM ingredients WHERE codi_ingredient = ingredientCode;

	UPDATE receptes SET calories=calories - calGram*quantity 
	WHERE codi_recepta=oldReceiptCode;

	UPDATE receptes SET calories=calories + calGram*quantity 
	WHERE codi_recepta=newReceiptCode;

END PROCEDURE;

CREATE TRIGGER tg_Update_Calories_UpdateOnCodiRecepta
  	UPDATE OF  codi_recepta ON Composades
  	REFERENCING OLD AS old NEW AS new 
  	FOR EACH ROW (EXECUTE PROCEDURE st_Update_Receipt_Calories_UpdateReceiptCodeOnComposades(old.codi_recepta, new.codi_recepta, old.codi_ingredient, old.quantitat_en_grams));



-------------------------------------------------------------------------------------------------------
-- EXERCICI 1.B
-------------------------------------------------------------------------------------------------------

CREATE PROCEDURE st_Validate_Dish_Less_500_cal(dishName LIKE Plats.nom_plat)

	DEFINE num int;
	DEFINE category varchar(100);
	
	LET category = (SELECT categoria FROM Plats WHERE nom_plat = dishName);
	IF (category="plat sa") THEN
	
		LET num = (SELECT count(*) FROM Fets_mitjancant fm, Receptes rec  WHERE fm.nom_plat = dishName AND fm.codi_recepta = rec.codi_recepta AND rec.calories > 500);
		
		IF ( num > 0) THEN
			RAISE EXCEPTION -746,0,"ERROR: The number of calories of a healthy dish is greater than 500.";
		END IF;
	
	END IF;

END PROCEDURE;


CREATE TRIGGER tg_Healthy_Dish_Less_500_cal_1
	UPDATE OF Categoria ON Plats
	REFERENCING NEW AS new
	FOR EACH ROW (EXECUTE PROCEDURE st_Validate_Dish_Less_500_cal(new.nom_plat));
	
CREATE TRIGGER tg_Healthy_Dish_Less_500_cal_2
	INSERT ON Fets_mitjancant 
	REFERENCING NEW AS new
	FOR EACH ROW (EXECUTE PROCEDURE st_Validate_Dish_Less_500_cal(new.nom_plat));

CREATE TRIGGER tg_Healthy_Dish_Less_500_cal_3
	UPDATE OF codi_recepta ON Fets_mitjancant
	REFERENCING NEW AS new
	FOR EACH ROW (EXECUTE PROCEDURE st_Validate_Dish_Less_500_cal(new.nom_plat));

---------------------------------------


CREATE PROCEDURE st_Validate_Receipt_Less_500_cal(receiptCode LIKE Receptes.codi_recepta, calories LIKE Receptes.calories)

	DEFINE num int;
	
	IF (calories > 500) THEN 
	
		LET num = (SELECT count(*) FROM Fets_mitjancant fm, Plats pl WHERE fm.nom_plat = pl.nom_plat AND fm.codi_recepta = receiptCode AND pl.categoria = "plat sa");
		
		IF ( num > 0) THEN
			RAISE EXCEPTION -746,0,"ERROR: The number of calories of a healthy dish is greater than 500.";
		END IF;
	
	END IF;

END PROCEDURE;

	
CREATE TRIGGER tg_Healthy_Dish_Less_500_cal_4
	UPDATE OF calories ON Receptes
	REFERENCING NEW AS new
	FOR EACH ROW (EXECUTE PROCEDURE st_Validate_Receipt_Less_500_cal(new.codi_recepta, new.calories));
	

	

 
COMMIT WORK;

--------------------------------------------------------------------------------------------------------